package jp.co.dailyReport.search.sql;

import java.util.Calendar;
import java.util.Vector;
import java.util.List;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.apache.log4j.Logger;
import jp.co.dailyReport.Executer;
import jp.co.dailyReport.search.SearchConditionOfWeeklyReport;
import jp.co.dailyReport.search.SearchWeeklyReport;
import jp.co.dailyReport.Utilities;

/**
 * 週報検索結果情報を取得するクラスです。
 */
public class SearchWeeklyReportLoadExecuter implements Executer {

	private static Logger log = Logger.getLogger(SearchWeeklyReportLoadExecuter.class);

	private StringBuffer sqlStatement;
	private List searchObjectList;
	private Integer customerId;
	private Integer pLStaffId;
	private Integer projectId;
	private java.sql.Date searchWorkDateStart;
	private java.sql.Date searchWorkDateEnd;
	private String problem;
	private String comment;

	public SearchWeeklyReportLoadExecuter(SearchConditionOfWeeklyReport searchConditionOfWeeklyReport) {
		searchObjectList = new ArrayList();
		sqlStatement = new StringBuffer();
		if(searchConditionOfWeeklyReport.getCustomerId()!=0) 
			setCustomerId(searchConditionOfWeeklyReport.getCustomerId());
		if(searchConditionOfWeeklyReport.getPLStaffId()!=0) 
			setPLStaffId(searchConditionOfWeeklyReport.getPLStaffId());
		if(searchConditionOfWeeklyReport.getProjectId()!=0) 
			setProjectId(searchConditionOfWeeklyReport.getProjectId());
		if(searchConditionOfWeeklyReport.getSearchWorkDateStart()!=null) 
			setSearchWorkDateStart(searchConditionOfWeeklyReport.getSearchWorkDateStart());
		if(searchConditionOfWeeklyReport.getSearchWorkDateEnd()!=null) 
			setSearchWorkDateEnd(searchConditionOfWeeklyReport.getSearchWorkDateEnd());
		if(searchConditionOfWeeklyReport.getKeyWord()!=null) {
			if(searchConditionOfWeeklyReport.getKeyWord().length()!=0) 
				setKeyWord(searchConditionOfWeeklyReport.getKeyWord());
		}

	}

	private void appendAND(String str){
		if(sqlStatement.length() > 0)
			sqlStatement.append(" AND " + str);
		else
			sqlStatement.append(str);
	}

	private void appendOR(String str){
		if(sqlStatement.length() > 0)
			sqlStatement.append(" OR " + str);
		else
			sqlStatement.append(str);
	}

	public void setCustomerId(int customerId) {
		this.customerId = new Integer(customerId);
		searchObjectList.add(this.customerId);
		appendAND("p.customerId = ?");
	}

	public void setPLStaffId(int pLStaffId) {
		this.pLStaffId = new Integer(pLStaffId);
		searchObjectList.add(this.pLStaffId);
		appendAND("p.pLStaffId = ?");
	}

	public void setProjectId(int projectId) {
		this.projectId = new Integer(projectId);
		searchObjectList.add(this.projectId);
		appendAND("w.projectId = ?");
	}

	public void setSearchWorkDateStart(Calendar searchWorkDateStart){
	  this.searchWorkDateStart = new java.sql.Date(searchWorkDateStart.getTime().getTime());
	  this.searchObjectList.add(this.searchWorkDateStart);
	  appendAND("w.workDateEnd >= ?");
	}

	public void setSearchWorkDateEnd(Calendar searchWorkDateEnd){
	  this.searchWorkDateEnd = new java.sql.Date(searchWorkDateEnd.getTime().getTime());
	  this.searchObjectList.add(this.searchWorkDateEnd);
	  appendAND("w.workDateEnd <= ?");
	}

	public void setKeyWord(String keyWord) {
		this.problem = keyWord;
		this.searchObjectList.add(this.problem);
		appendAND("(" + "w.problem like ?");

		this.comment = keyWord;
		this.searchObjectList.add(this.comment);
		appendOR("w.comment like ?" + ")");
	}

	/**
	 * 設定された条件を基に、週報検索結果情報を取得します。
	 * 
	 * @param conn コネクションオブジェクト
	 * @return 週報検索結果オブジェクトの配列
	 * @throws SQLException
	 */
	public Object[] execute(Connection conn) throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rset = null;
		Vector answer = new Vector();

		String sql = "SELECT DISTINCT w.weeklyReportId weeklyReportId,w.projectId projectId," +
					 "w.workDateEnd workDateEnd,p.name name " +
					 "FROM weeklyReport AS w " +
					 "LEFT JOIN project AS p ON w.projectId = p.projectId";

		if(sqlStatement.length() > 0){
			sql = sql + " where " + sqlStatement.toString() + " order by w.workDateStart DESC,w.weeklyReportId";
		} else {
			sql = sql + " order by w.workDateStart DESC,w.weeklyReportId";
		}
		log.debug("sql: " + sql);

        try {
			pstmt = conn.prepareStatement(sql);

			if(this.customerId != null){
				pstmt.setInt(this.searchObjectList.indexOf(this.customerId)+1, this.customerId.intValue());
				releaseSearchObject(this.customerId);
			}

			if(this.pLStaffId != null){
				pstmt.setInt(this.searchObjectList.indexOf(this.pLStaffId)+1, this.pLStaffId.intValue());
				releaseSearchObject(this.pLStaffId);
			}

			if(this.projectId != null){
				pstmt.setInt(this.searchObjectList.indexOf(this.projectId)+1, this.projectId.intValue());
				releaseSearchObject(this.projectId);
			}

			if(this.searchWorkDateStart != null){
				pstmt.setDate(this.searchObjectList.indexOf(this.searchWorkDateStart)+1, this.searchWorkDateStart);
				releaseSearchObject(this.searchWorkDateStart);
			}

			if(this.searchWorkDateEnd != null){
				pstmt.setDate(this.searchObjectList.indexOf(this.searchWorkDateEnd)+1, this.searchWorkDateEnd);
				releaseSearchObject(this.searchWorkDateEnd);
			}

			if(this.problem != null){
				pstmt.setString(this.searchObjectList.indexOf(this.problem)+1, "%" + this.problem + "%");
				releaseSearchObject(this.problem);
			}

			if(this.comment != null){
				pstmt.setString(this.searchObjectList.indexOf(this.comment)+1, "%" + this.comment + "%");
				releaseSearchObject(this.comment);
			}
			rset = pstmt.executeQuery();
			while(rset.next()){
				SearchWeeklyReport searchWeeklyReport = new SearchWeeklyReport();
				searchWeeklyReport.setWeeklyReportId(rset.getInt("weeklyReportId"));
				searchWeeklyReport.setProjectId(rset.getInt("projectId"));
				searchWeeklyReport.setProjectName(Utilities.convUniqueCharacter(rset.getString("name")));
				searchWeeklyReport.setSearchWorkDate(rset.getDate("workDateEnd"));
				answer.add(searchWeeklyReport);
			}
		} finally {
			if(rset != null)
				rset.close();
			if(pstmt != null)
				pstmt.close();
		}
		return (SearchWeeklyReport[])answer.toArray(new SearchWeeklyReport[0]);
	}

	private void releaseSearchObject(Object obj){
		searchObjectList.set(searchObjectList.indexOf(obj), null);
	}
}
